-- STANDARD SCRIPT
-- INSERT DATA IN insurance2_results_t
--
--


------------------------------------------------
-- FUNCTION TO INSERT NEW ROWS INTO ACCOUNT TABLE


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION upd_insurance2_results_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
  v_hit_commercial integer;
  v_hit_tpl integer;
  v_hit_medicaid integer;
  v_hit_medicare integer;
  v_unique_hit integer;

BEGIN

SELECT INTO v_batch_run_date our_run_date from batch_insurance2_t limit 1;
SELECT INTO v_cust_id cust_id from batch_insurance2_t limit 1;

PERFORM ml_upd_batch_status_insurance2_new_f(v_cust_id,v_batch_run_date,1,4,'START INSERT IN insurance2_results_t');



-- STEP 1 INSERT RECORDS INTO insurance2_results_t    

RAISE NOTICE 'START INSERT INTO insurance2_results_t TABLE';
  
  INSERT INTO insurance2_results_t
  SELECT
     *
  FROM
     (select distinct on (hid,acctnum,carrier_code) * from batch_insurance2_t order by hid,acctnum,carrier_code,active_flag desc) as a
  WHERE NOT EXISTS
    (SELECT 1 FROM insurance2_results_t b WHERE (a.cust_id = b.cust_id AND a.acctnum = b.acctnum AND a.hid = b.hid and a.carrier_code = b.carrier_code) )
  ; 

GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'DONE INSERT INTO insurance2_results_t TABLE : %', v_status;


/*
SELECT INTO v_hit_tpl        sum(case when emdeon_ins_type = 4 THEN 1 else 0 end) from batch_insurance2_t where active_flag = 1 and suppress_flag = 0;
SELECT INTO v_hit_commercial sum(case when emdeon_ins_type = 3 THEN 1 else 0 end) from batch_insurance2_t where active_flag = 1 and suppress_flag = 0;
SELECT INTO v_hit_medicaid   sum(case when emdeon_ins_type = 2 THEN 1 else 0 end) from batch_insurance2_t where active_flag = 1 and suppress_flag = 0;
SELECT INTO v_hit_medicare   sum(case when emdeon_ins_type = 1 THEN 1 else 0 end) from batch_insurance2_t where active_flag = 1 and suppress_flag = 0;
SELECT INTO v_unique_hit     count(*) from (select distinct on (hid,acctnum) hid,acctnum 
                                             from batch_insurance2_t where active_flag = 1 and suppress_flag = 0
                                           ) as a;
*/

SELECT INTO v_hit_tpl        sum(case when emdeon_ins_type = 'T' THEN 1 else 0 end) from insurance2_results_t 
                             where cust_id = v_cust_id and our_run_date = v_batch_run_date and active_flag = 1 and suppress_flag = 0;
SELECT INTO v_hit_commercial sum(case when emdeon_ins_type = 'C' THEN 1 else 0 end) from insurance2_results_t 
                             where cust_id = v_cust_id and our_run_date = v_batch_run_date and active_flag = 1 and suppress_flag = 0;
SELECT INTO v_hit_medicaid   sum(case when emdeon_ins_type = 'D' THEN 1 else 0 end) from insurance2_results_t 
                             where cust_id = v_cust_id and our_run_date = v_batch_run_date and active_flag = 1 and suppress_flag = 0;
SELECT INTO v_hit_medicare   sum(case when emdeon_ins_type = 'M' THEN 1 else 0 end) from insurance2_results_t 
                             where cust_id = v_cust_id and our_run_date = v_batch_run_date and active_flag = 1 and suppress_flag = 0;
SELECT INTO v_unique_hit     count(*) from (select distinct on (hid,acctnum) hid,acctnum
                                             from insurance2_results_t 
                                             where cust_id = v_cust_id and our_run_date = v_batch_run_date and active_flag = 1 and suppress_flag = 0
                                           ) as a;


-- STEP 2 UPDATE batch_status_insurance2_t WITH THE NUMBER INSERTED 

  UPDATE batch_status_insurance2_t
    SET
      insurance2_results_inserted = v_status,
      unique_hit     = v_unique_hit,
      commercial_hit = v_hit_commercial,
      tpl_hit        = v_hit_tpl,
      medicaid_hit   = v_hit_medicaid,
      medicare_hit   = v_hit_medicare
    WHERE
      (batch_status_insurance2_t.last_run_date = v_batch_run_date and batch_status_insurance2_t.cust_id = v_cust_id);

PERFORM ml_upd_batch_status_insurance2_new_f(v_cust_id,v_batch_run_date,1,4,'FINISH INSERT IN insurance2_results_t');



  return v_status;
END;
$$ LANGUAGE plpgsql;
